package com.buaa.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

public class ETLUtils {

	private String institution_path = "D:/1.xlsx";
	private String instrument_path = "D:/2.xlsx";
	private String resource_path = "D:/3.xlsx";

	// public static Map<Integer, String> type = new HashMap<Integer, String>()
	// {
	// private static final long serialVersionUID = 1L;
	//
	// {
	// put(1, "其他企业");
	// put(2, "检测单位");
	// put(3, "生产力促进中心");
	// put(4, "制造企业");
	// put(5, "孵化器");
	// put(6, "工程技术研究中心");
	// put(7, "重点实验室");
	// put(8, "大学科技园");
	// put(9, "企业");
	// put(10, "高新区");
	// put(11, "企业研发中心");
	// put(12, "科研院所");
	// put(13, "高等院校");
	// put(14, "大学");
	// put(15, "产业基地");
	// put(16, "地市管理单位");
	// put(17, "联盟");
	// put(18, "中试基地");
	// put(19, "其他");
	// }
	// };

	/**
	 * 表所有字段最大长度汇总
	 * 
	 * @throws Exception
	 */
	@Test
	public void sumField() throws Exception {
		// institution_path 22
		sumFieldHandle(institution_path, 22);
		// for (int i = 0; i < 22; i++) {
		// fieldHandle(institution_path, i);
		// }

		// instrument_path 37
		sumFieldHandle(institution_path, 37);
		// for (int i = 0; i < 37; i++) {
		// fieldHandle(instrument_path, i);
		// }

		// resource 26
		sumFieldHandle(resource_path, 26);
		// for (int i = 0; i < 26; i++) {
		// fieldHandle(resource_path, i);
		// }
	}

	/**
	 * 表单个字段最大长度
	 * 
	 * @throws Exception
	 */
	@Test
	public void singleField() throws Exception {
		// institution_path 22
		// fieldHandle(institution_path, 1);
		// instrument_path 37
		fieldHandle(instrument_path, 1);
	}

	// /**
	// * 表单个字段最大长度
	// *
	// * @throws Exception
	// */
	// private void fieldHandle(String path, int nthField) throws Exception {
	// InputStream ExcelFileToRead = new FileInputStream(path);
	// XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
	// XSSFSheet sheet = wb.getSheetAt(0);
	// XSSFRow row;
	// XSSFCell cell = null;
	// String data;
	// int max = Integer.MIN_VALUE;
	// int id = Integer.MIN_VALUE;
	// int cur = 0;
	//
	// Iterator<Row> rows = sheet.rowIterator();
	// while (rows.hasNext()) {
	// row = (XSSFRow) rows.next();
	// cur++;
	// if (cur == 1) {
	// continue;
	// }
	// Iterator<Cell> cells = row.cellIterator();
	// for (int i = 0; i < nthField; i++) {
	// cell = (XSSFCell) cells.next();
	// }
	// data = cell.getStringCellValue();
	// if (data.length() > max) {
	// max = data.length();
	// id = cur;
	// }
	// }
	// wb.close();
	// System.out.println(nthField + " : " + "A" + id + " : " + max);
	// }

	/**
	 * 表所有字段最大长度
	 * 
	 * @param path
	 * @param length
	 * @throws Exception
	 */
	private void sumFieldHandle(String path, int length) throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(path);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell = null;
		String data;
		int[] max = new int[length];
		int[] id = new int[length];
		int cur = 0;
		// 增强for循环，底层迭代器，只能遍历
		for (int i = 0; i < max.length; i++) {
			max[i] = Integer.MIN_VALUE;
		}
		for (int i = 0; i < id.length; i++) {
			id[i] = Integer.MIN_VALUE;
		}

		Iterator<Row> rows = sheet.rowIterator();
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur == 1) {
				continue;
			}
			for (int i = 0; i < length; i++) {
				cell = row.getCell(i);
				if (cell != null) {
					if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
						data = cell.getStringCellValue();
					} else {
						data = cell.getRawValue();
					}
					if (data.length() > max[i]) {
						max[i] = data.length();
						id[i] = cur;
					}
				}
			}
		}
		wb.close();
		for (int i = 0; i < length; i++)
			System.out.println(i + " : " + "A" + id[i] + " : " + max[i]);
	}

	/**
	 * 表单个字段最大长度
	 * 
	 * @throws Exception
	 */
	private void fieldHandle(String path, int nthField) throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(path);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell = null;
		String data;
		int max = Integer.MIN_VALUE;
		int id = Integer.MIN_VALUE;
		int cur = 0;

		Iterator<Row> rows = sheet.rowIterator();
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur == 1) {
				continue;
			}
			cell = row.getCell(nthField);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
				} else {
					data = cell.getRawValue();
				}
				if (data.length() > max) {
					max = data.length();
					id = cur;
				}
			}
		}
		wb.close();
		System.out.println(nthField + " : " + "A" + id + " : " + max);
	}

}
